﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace DBUtil
{
    public class Sql_BaseDAL
    {
        private SqlParameter[] parameter;
        protected string _sqlConnectionString;  //数据库连接字符串

        #region---传递链接字符串---
        /// <summary>
        /// 传递链接字符串
        /// </summary>
        public string SqlConnectionString
        {
            get
            {
                return _sqlConnectionString;
            }
            set
            {
                _sqlConnectionString = value;
            }
        }
        #endregion        

        #region---传递执行SQL过程参数的数组---
        /// <summary>
        /// 传递执行SQL过程参数的数组
        /// </summary>
        public SqlParameter[] myParameter
        {
            get
            {
                return parameter;
            }
            set
            {
                parameter = value;
            }
        }
        #endregion        

        #region  ---根据SQL语句计算查询结果记录数---
        /// <summary>
        /// 根据SQL语句计算查询结果记录数
        /// </summary>
        /// <param name="Message">out 出异常信息</param>
        /// <param name="SQL">SQL语句</param>
        /// <returns>返回 int类型, 默认为0 时为不成功</returns>
        public int SqlStr_CountRec(out string Message, string SQL)
        {
            int i = 0;
            Message = "";
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    SqlCommand MyCommand = new SqlCommand(SQL, MyConnection);
                    MyConnection.Open();
                    i = (int)MyCommand.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    Message = ex.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return i;
        }
        #endregion

        #region "---根据SQL语句数据提交，添加一条记录到数据库---"
        /// <summary>
        ///  根据SQL语句数据提交，添加一条记录到数据库，返回一个bool 值, 成功返回true; 失败返回false
        /// </summary>
        /// <param name="Message">out 出异常信息</param>
        /// <param name="_select">SQL字符串</param>
        /// <returns>return bool  返回一个bool 成功返回true; 失败返回false</returns>
        public bool SqlStr_UpdateTry(out string Message, string _select)
        {
            bool a1 = false;
            Message = "";
            SqlTransaction MyTran = null;
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    MyConnection.Open();
                    //事务必须要从一个连接建立
                    MyTran = MyConnection.BeginTransaction(IsolationLevel.RepeatableRead, "MyUpdate");
                    //SqlCommand 必须要从这个连接中创建  SqlConnection, SqlTransaction, SqlCommand 三个A->B->C关系
                    SqlCommand MyCommand = MyConnection.CreateCommand();
                    MyCommand.Connection = MyConnection;
                    MyCommand.Transaction = MyTran;
                    MyCommand.CommandText = _select;
                    if (MyCommand.ExecuteNonQuery() != 0)
                    {
                        MyTran.Commit();   //提交
                        a1 = true;
                    }
                    else
                    {
                        MyTran.Rollback("MyUpdate");
                        a1 = false;
                    }
                }
                catch (Exception e)
                {
                    try
                    {
                        if (MyTran != null)
                            MyTran.Rollback("MyUpdate");
                    }
                    catch { }
                    Message = e.Message;
                    a1 = false;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return a1;
        }
        #endregion

        #region "---根据参数化 SQL语句数据提交，添加一条记录到数据库---"
        /// <summary>
        ///  根据参数化 SQL语句数据提交，添加一条记录到数据库，返回一个bool 值, 成功返回true; 失败返回false
        /// </summary>
        /// <param name="_select">SQL字符串</param>
        /// <param name="boolparameter">是否有传入参数</param>	
        /// <returns>return bool  返回一个bool 表示成功</returns>
        /// 
        public bool SqlStr_UpdateTry_sqlparams(out string Message, string _select)
        {
            bool a1 = false;
            Message = "";
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    SqlCommand MyCommand = new SqlCommand(_select, MyConnection);
                    MyConnection.Open();
                    if (MyCommand.ExecuteNonQuery() != 0)
                    {
                        a1 = true;
                    }
                    else
                    {
                        a1 = false;
                    }
                }
                catch (Exception e)
                {
                    Message = e.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return a1;
        }
        #endregion

        #region ----根据SQL语句读取一个字段----
        /// <summary>
        /// 根据SQL语句读取一个字段数据
        /// </summary>
        /// <param name="_Select">SQL字符串</param>	
        /// <param name="_Field">字段名称</param>				
        /// <returns></returns>
        public string SqlStr_ReadField(out string Message, string _Select, string _Field)
        {
            Message = "";
            string _MyReadStr = "";
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    MyConnection.Open();
                    SqlCommand MyCommand = new SqlCommand(_Select, MyConnection);
                    SqlDataReader MyDataReader = MyCommand.ExecuteReader();
                    if (MyDataReader.Read())
                        _MyReadStr = MyDataReader[_Field].ToString().Trim();
                    MyDataReader.Close();
                }
                catch (Exception e)
                {
                    Message = e.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return _MyReadStr;
        }
        #endregion

        #region ---根据SQL语句判断读取数据,返回一个bool 值, 成功返回true; 失败返回false----
        /// <summary>
        /// 根据SQL语句判断读取数据,返回一个bool 值, 成功返回true; 失败返回false
        /// </summary>
        /// <param name="_Select">SQL字符串</param>					
        /// <returns></returns>
        public bool SqlStr_Bool_ReadFiled(out string Message, string _Select)
        {
            Message = "";
            bool i = false;
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    MyConnection.Open();
                    SqlCommand MyCommand = new SqlCommand(_Select, MyConnection);
                    SqlDataReader MyDataReader = MyCommand.ExecuteReader();
                    i = MyDataReader.Read();
                    MyDataReader.Close();
                }
                catch (Exception e)
                {
                    Message = e.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return i;
        }
        #endregion

        #region "---根据SQL语句查询数据库,生成一个数据集DataSet---"
        /// <summary>
        /// 根据SQL语句查询数据库,生成一个数据集DataSet		
        /// </summary>
        /// <param name="_selectstr">SQL查询语句</param>		
        /// <returns>return DataSet  查询数据库,生成一个数据集DataSet</returns>		
        public DataSet SqlStr_GetDataSet(out string Message, string _selectstr)
        {
            Message = "";
            DataSet MyDataSet = new DataSet();
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                SqlDataAdapter MyDataAdapter = new SqlDataAdapter(_selectstr, MyConnection);
                try
                {
                    MyConnection.Open();
                    MyDataAdapter.Fill(MyDataSet);
                }
                catch (Exception e)
                {
                    Message = e.Message;
                }
                finally
                {
                    MyDataAdapter.Dispose();
                    MyConnection.Close();
                }
            }
            return MyDataSet;
        }
        #endregion

        #region --使用存储过程，结果返回int--
        /// <summary>
        /// 使用存储过程，返回INT   存储过程必须是用select + 整型参数 返回 
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>	
        /// <param name="boolparameter">是否有传入参数</param>	
        /// <returns>返回INT代码</returns>		
        public int SqlProcedure_int(out string Message, string ProcedureName, bool boolparameter)
        {
            Message = "";
            int Procedint = -1;
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    SqlDataAdapter MyDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                    SqlCommand MyCommand = new SqlCommand(ProcedureName, MyConnection);
                    if (boolparameter)
                    {
                        foreach (SqlParameter parm in parameter)
                        {
                            MyCommand.Parameters.Add(parm);
                            MyCommand.CommandType = CommandType.StoredProcedure;
                        }
                    }
                    MyConnection.Open();
                    Procedint = int.Parse(MyCommand.ExecuteScalar().ToString());   //接受SQL的  return int 值
                }
                catch (Exception e1)
                {
                    Message = e1.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return Procedint;
        }
        #endregion

        #region --使用存储过程，结果返回int--
        /// <summary>
        /// 使用存储过程，返回INT
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>	
        /// <param name="boolparameter">是否有传入参数</param>	
        /// <returns>返回INT代码</returns>		
        public int SqlProcedure_int(out string Message, string ProcedureName, bool boolparameter, out object ReturnValue, string refValue)
        {
            Message = "";
            ReturnValue = null;
            int Procedint = -1;
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                SqlDataAdapter MyDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                SqlCommand MyCommand = new SqlCommand(ProcedureName, MyConnection);
                if (boolparameter)
                {
                    foreach (SqlParameter parm in parameter)
                    {
                        MyCommand.Parameters.Add(parm);
                        MyCommand.CommandType = CommandType.StoredProcedure;
                    }
                }
                try
                {
                    MyConnection.Open();
                    Procedint = (int)MyCommand.ExecuteScalar();   //接受SQL的  return int 值
                    MyDataAdapter.SelectCommand = MyCommand;
                    ReturnValue = MyCommand.Parameters[refValue].Value;
                }
                catch (Exception e1)
                {
                    Message = e1.Message;
                }
                finally
                {
                    MyCommand.Parameters.Clear();
                    MyConnection.Close();
                }
            }
            return Procedint;
        }
        #endregion

        #region --使用存储过程，结果返回Str--
        /// <summary>
        /// 使用存储过程，返回Str
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>	
        /// <param name="boolparameter">是否有传入参数</param>	
        /// <returns>返回Str代码</returns>		
        public string SqlProcedure_str(out string Message, string ProcedureName, bool boolparameter)
        {
            Message = "";
            string ProcedStr = "";
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                SqlDataAdapter MyDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                SqlCommand MyCommand = new SqlCommand(ProcedureName, MyConnection);
                if (boolparameter)
                {
                    foreach (SqlParameter parm in parameter)
                    {
                        MyCommand.Parameters.Add(parm);
                        MyCommand.CommandType = CommandType.StoredProcedure;
                    }
                }
                try
                {
                    MyConnection.Open();
                    ProcedStr = MyCommand.ExecuteScalar().ToString();   //接受SQL的  return int 值
                }
                catch (Exception e1)
                {
                    Message = e1.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return ProcedStr;
        }
        #endregion

        #region --使用存储过程，结果返回Str--
        /// <summary>
        /// 使用存储过程，返回Str
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>	
        /// <param name="boolparameter">是否有传入参数</param>	
        /// <returns>返回Str代码</returns>		
        public string SqlProcedure_str(out string Message, string ProcedureName, bool boolparameter, out object ReturnValue, string refValue)
        {
            Message = "";
            string ProcedStr = "";
            ReturnValue = null;
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                SqlDataAdapter MyDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                SqlCommand MyCommand = new SqlCommand(ProcedureName, MyConnection);
                if (boolparameter)
                {
                    foreach (SqlParameter parm in parameter)
                    {
                        MyCommand.Parameters.Add(parm);
                        MyCommand.CommandType = CommandType.StoredProcedure;
                    }
                }
                try
                {
                    MyConnection.Open();
                    object _tempobj = MyCommand.ExecuteScalar();
                    if (_tempobj != null)
                        ProcedStr = _tempobj.ToString();                   

                    MyDataAdapter.SelectCommand = MyCommand;
                    ReturnValue = MyCommand.Parameters[refValue].Value;
                }
                catch (Exception e1)
                {
                    Message = e1.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return ProcedStr;
        }
        #endregion

        #region --使用存储过程，结果返回object--
        /// <summary>
        /// 使用存储过程，返回object
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>	
        /// <param name="boolparameter">是否有传入参数</param>	
        /// <returns>返回object代码</returns>		
        public object SqlProcedure_object(out string Message, string ProcedureName, bool boolparameter)
        {
            Message = "";
            object Procedobject = null;
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    SqlDataAdapter MyDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                    SqlCommand MyCommand = new SqlCommand(ProcedureName, MyConnection);
                    if (boolparameter)
                    {
                        foreach (SqlParameter parm in parameter)
                        {
                            MyCommand.Parameters.Add(parm);
                            MyCommand.CommandType = CommandType.StoredProcedure;
                        }
                    }
                    MyConnection.Open();
                    Procedobject = (object)MyCommand.ExecuteScalar();   //接受SQL的  return int 值
                }
                catch (Exception e1)
                {
                    Message = e1.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return Procedobject;
        }
        #endregion

        #region --使用存储过程，结果返回DataSet--
        /// <summary>
        /// 使用存储过程，返回DataSet
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>	
        /// <param name="boolparameter">是否有传入参数</param>	
        /// <returns>返回DataSet</returns>		
        public DataSet SqlProcedure_DataSet(out string Message, string ProcedureName, bool boolparameter)
        {
            Message = "";
            DataSet MyDataSet = new DataSet();
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    SqlDataAdapter MyDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                    SqlCommand MyCommand = new SqlCommand(ProcedureName, MyConnection);
                    MyCommand.CommandType = CommandType.StoredProcedure;
                    if (boolparameter)
                    {
                        foreach (SqlParameter parm in parameter)
                        {
                            MyCommand.Parameters.Add(parm);
                            MyCommand.CommandType = CommandType.StoredProcedure;
                        }
                    }
                    MyCommand.CommandTimeout = 10000;
                    MyDataAdapter.SelectCommand = MyCommand;
                    MyConnection.Open();
                    MyDataAdapter.Fill(MyDataSet);
                }
                catch (Exception e1)
                {
                    Message = e1.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return MyDataSet;
        }
        #endregion

        #region --使用存储过程，结果返回DataTable--
        /// <summary>
        /// 使用存储过程，返回DataTable
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>	
        /// <param name="boolparameter">是否有传入参数</param>	
        /// <returns>返回DataTable</returns>		
        public DataTable SqlProcedure_DataTable(out string Message, string ProcedureName, bool boolparameter)
        {
            Message = "";
            DataTable MyDataTable = new DataTable();
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    SqlDataAdapter MyDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                    SqlCommand MyCommand = new SqlCommand(ProcedureName, MyConnection);
                    if (boolparameter)
                    {
                        foreach (SqlParameter parm in parameter)
                        {
                            MyCommand.Parameters.Add(parm);
                            MyCommand.CommandType = CommandType.StoredProcedure;
                        }
                    }
                    MyCommand.CommandTimeout = 10000;
                    MyDataAdapter.SelectCommand = MyCommand;
                    MyConnection.Open();
                    MyDataAdapter.Fill(MyDataTable);
                }
                catch (Exception e1)
                {
                    Message = e1.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return MyDataTable;
        }



        public DataTable SqlProcedure_DataTable(out string Message, string ProcedureName, bool boolparameter, out object ReturnValue, string refValue)
        {
            Message = "";
            ReturnValue = null;
            DataTable MyDataTable = new DataTable();
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    SqlDataAdapter MyDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                    SqlCommand MyCommand = new SqlCommand(ProcedureName, MyConnection);
                    if (boolparameter)
                    {
                        foreach (SqlParameter parm in parameter)
                        {
                            MyCommand.Parameters.Add(parm);
                            MyCommand.CommandType = CommandType.StoredProcedure;
                        }
                    }
                    MyCommand.CommandTimeout = 10000;
                    MyDataAdapter.SelectCommand = MyCommand;
                    MyConnection.Open();
                    MyDataAdapter.Fill(MyDataTable);
                    MyDataAdapter.SelectCommand = MyCommand;
                    ReturnValue = MyCommand.Parameters[refValue].Value;
                }
                catch (Exception e1)
                {
                    Message = e1.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return MyDataTable;
        }
        #endregion

        #region --使用存储过程，结果返回DataTable(分页需要用到)--
        /// <summary>
        /// 使用存储过程，返回DataTable 返回查询Table分页数  分页需要用到
        /// </summary>
        /// <param name="Message">异常信息</param>
        /// <param name="ProcedureName">过程名称</param>
        /// <param name="boolparameter">过程是否带有入口参数</param>
        /// <param name="PageCount">分页数</param>
        /// <returns>返回 DataTable</returns>
        public DataTable SqlProcedure_DataTable(out string Message, string ProcedureName, bool boolparameter, out int PageCount)
        {
            Message = "";
            PageCount = 0;
            DataTable MyDataTable = new DataTable();
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    SqlDataAdapter MyDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                    SqlCommand MyCommand = new SqlCommand(ProcedureName, MyConnection);
                    if (boolparameter)
                    {
                        foreach (SqlParameter parm in parameter)
                        {
                            MyCommand.Parameters.Add(parm);
                            MyCommand.CommandType = CommandType.StoredProcedure;
                        }
                    }
                    MyCommand.CommandTimeout = 10000;
                    MyDataAdapter.SelectCommand = MyCommand;
                    MyConnection.Open();
                    MyDataAdapter.Fill(MyDataTable);
                    MyDataAdapter.SelectCommand = MyCommand;
                    object aa = MyCommand.Parameters["@Counts"].Value;
                    //string messtr = getDataException(ProcedureName, SqlConnectionString, "", parameter);
                    //System.Web.HttpContext.Current.Response.Write(messtr);
                    //System.Web.HttpContext.Current.Response.End();
                    PageCount = (int)MyCommand.Parameters["@Counts"].Value;
                }
                catch (Exception e1)
                {
                    Message = e1.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return MyDataTable;
        }
        #endregion
        /// <summary>
        /// 添加错误信息
        /// </summary>
        /// <param name="strProcName">执行的SQL或者存储过程名称</param>
        /// <param name="strConnstring">连接语句</param>
        /// <param name="ExceptionText">错误信息</param>
        /// <param name="parameters">SQL或者存储过程参数</param>
        /// <returns>组合好的错误信息</returns>
        public static string getDataException(string strProcName, string strConnstring, string ExceptionText, params SqlParameter[] parameters)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<div style=\"font-size:12px\">");
            sb.Append("数据操作错误：<br/>");
            sb.AppendFormat("<span style='color:red'> 提示错误信息：{0}<span>", ExceptionText);
            sb.AppendFormat("连接字符串为：{0}<br/>", strConnstring);
            sb.AppendFormat("执行的SQL/存储过程名称为：{0}<br/>", strProcName);
            sb.Append("<table style=\"font-size:12px\">");
            if (parameters != null)
            {
                foreach (SqlParameter para in parameters)//循环参数
                {
                    sb.AppendFormat("<tr><td<参数名：</td><td>{0}</td><td> 参数类型：</td><td>{1}</td><td>  参数值：</td><td>{2}</td></tr>", para.ParameterName, para.SqlDbType, para.Value);
                }
            }
            sb.Append("</table>");
            sb.Append("</div>");
            return sb.ToString();
        }
   

        #region --使用事务处理，存储过程作为参数 结果返回int --
        /// <summary>
        /// 事务处理
        /// </summary>
        /// <param name="Message"></param>
        /// <param name="ProcdureName">存储过程名</param>
        /// <param name="Params">存储过程参数数组 类型ArrayList 表示为一组 SqlParameter[]</param>
        /// <returns>对一个存储过程的事务处理</returns>
        public int SqlProcedureByTrans(out string Message, string ProcdureName, ArrayList Params)
        {
            Message = "";
            int count = 0;
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                SqlTransaction trans = null;
                try
                {
                    MyConnection.Open();
                    trans = MyConnection.BeginTransaction("mytrans");
                    SqlCommand MyCommand = new SqlCommand(ProcdureName, MyConnection);
                    foreach (SqlParameter[] parameters in Params)
                    {
                        MyCommand.Parameters.Clear();
                        foreach (SqlParameter parm in parameters)
                        {
                            MyCommand.Parameters.Add(parm);
                        }
                        MyCommand.CommandType = CommandType.StoredProcedure;
                        MyCommand.Transaction = trans;
                        count += (int)MyCommand.ExecuteScalar();
                    }
                    trans.Commit();
                }
                catch (Exception e1)
                {
                    Message = e1.Message;
                    try
                    {
                        trans.Rollback("mytrans");
                    }
                    catch (Exception e2) { Message += "回滚数据 " + e2.Message; }
                    count = 0;
                }
                finally
                {
                    if (trans != null)
                        trans.Dispose();
                    MyConnection.Close();
                }
            }
            return count;
        }
        #endregion

        #region 按条件过滤过滤
        /// <summary>
        /// 按 Filter 条件在末地表中过滤
        /// </summary>
        /// <param name="temptable">原表</param>
        /// <param name="Filter">过滤条件</param>
        /// <returns>返回 DataTable 传入table如有记录可过滤则返回过滤表, 如无记录则返回原表</returns>
        public static DataTable FilterDataTable(DataTable temptable, string Filter)
        {
            try
            {
                if (temptable.Rows.Count > 0)
                {
                    DataTable tempTB;
                    tempTB = temptable.Clone();
                    DataRow[] tempDR = temptable.Select(Filter);
                    foreach (DataRow row in tempDR)
                    {
                        tempTB.ImportRow(row);
                    }
                    return tempTB;
                }
                else
                {
                    return temptable;
                }
            }
            catch
            {
                return temptable;
            }
        }
        #endregion

        #region 执行带参数的SQL语句
       /// <summary>
        /// 执行SQL语句，参数为参数数组Parameters ，返回影响的记录数
       /// </summary>
        /// <param name="SQLString">SQL语句</param>
       /// <param name="cmdParms">参数数组</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(out string message, string SQLString, bool boolparameter)
        {
            message = "";
            int rows = 0;
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {                
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        if (MyConnection.State != ConnectionState.Open)
                            MyConnection.Open();
                        cmd.Connection = MyConnection;
                        cmd.CommandText = SQLString;

                        cmd.CommandType = CommandType.Text;

                        if (boolparameter)
                        {
                            foreach (SqlParameter parm in parameter)
                            {
                                cmd.Parameters.Add(parm);
                            }
                        }

                        rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        message = E.Message;
                    }  
                }                
            }
            return rows;
        }
        #endregion

        #region "---执行带参数的SQL语句查询，返回查询结果数据集DataSet---"
        /// <summary>
        /// 执行带参数的SQL语句查询，返回查询结果数据集DataSet
        /// </summary>
        /// <param name="_selectstr">SQL查询语句</param>		
        /// <returns>return DataSet  查询数据库,生成一个数据集DataSet</returns>		
        public DataSet SqlStr_GetDataSet(out string Message, string _selectstr, bool boolparameter)
        {
            Message = "";
            DataSet MyDataSet = new DataSet();
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    SqlDataAdapter MyDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                    SqlCommand MyCommand = new SqlCommand(_selectstr, MyConnection);
                    MyCommand.CommandType = CommandType.Text;
                    if (boolparameter)
                    {
                        foreach (SqlParameter parm in parameter)
                        {
                            MyCommand.Parameters.Add(parm);
                        }
                    }
                    MyCommand.CommandTimeout = 10000;
                    MyDataAdapter.SelectCommand = MyCommand;
                    MyConnection.Open();
                    MyDataAdapter.Fill(MyDataSet);
                }
                catch (Exception e1)
                {
                    Message = e1.Message;
                }
                finally
                {
                    MyConnection.Close();
                }
            }
            return MyDataSet;
        }
        #endregion

        #region 执行带参数的SQL语句查询结果语句，返回查询结果（object）
        /// <summary>
        /// 执行带参数的SQL语句查询结果语句，返回查询结果（object）
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public object GetSingle(string SQLString, bool boolparameter, out string Message)
        {
            Message = "";
            object _myobj = null;
            using (SqlConnection MyConnection = new SqlConnection(SqlConnectionString))
            {                
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        if (MyConnection.State != ConnectionState.Open)
                            MyConnection.Open();
                        cmd.Connection = MyConnection;
                        cmd.CommandText = SQLString;

                        cmd.CommandType = CommandType.Text;

                        if (boolparameter)
                        {
                            foreach (SqlParameter parm in parameter)
                            {
                                cmd.Parameters.Add(parm);
                            }
                        }

                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            _myobj = null;
                        }
                        else
                        {
                            _myobj = obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        Message = e.Message;
                    }        
                }                        
            }
            return _myobj; 
        }
        #endregion
    }
}
